﻿namespace Hidistro.SqlDal.Store
{
    using Hidistro.Entities;
    using Hidistro.Entities.Store;
    using Microsoft.Practices.EnterpriseLibrary.Data;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Runtime.InteropServices;

    public class VoteDao
    {
        private Database database = DatabaseFactory.CreateDatabase();

        public long CreateVote(VoteInfo vote)
        {
            DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_Votes_Create");
            this.database.AddInParameter(storedProcCommand, "VoteName", DbType.String, vote.VoteName);
            this.database.AddInParameter(storedProcCommand, "IsBackup", DbType.Boolean, vote.IsBackup);
            this.database.AddInParameter(storedProcCommand, "MaxCheck", DbType.Int32, vote.MaxCheck);
            this.database.AddOutParameter(storedProcCommand, "VoteId", DbType.Int64, 8);
            long parameterValue = 0L;
            if (this.database.ExecuteNonQuery(storedProcCommand) > 0)
            {
                parameterValue = (long) this.database.GetParameterValue(storedProcCommand, "VoteId");
            }
            return parameterValue;
        }

        public int CreateVoteItem(VoteItemInfo voteItem, DbTransaction dbTran)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("INSERT INTO Hishop_VoteItems(VoteId, VoteItemName, ItemCount) Values(@VoteId, @VoteItemName, @ItemCount)");
            this.database.AddInParameter(sqlStringCommand, "VoteId", DbType.Int64, voteItem.VoteId);
            this.database.AddInParameter(sqlStringCommand, "VoteItemName", DbType.String, voteItem.VoteItemName);
            this.database.AddInParameter(sqlStringCommand, "ItemCount", DbType.Int32, voteItem.ItemCount);
            if (dbTran == null)
            {
                return this.database.ExecuteNonQuery(sqlStringCommand);
            }
            return this.database.ExecuteNonQuery(sqlStringCommand, dbTran);
        }

        public int DeleteVote(long voteId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Hishop_Votes WHERE VoteId = @VoteId");
            this.database.AddInParameter(sqlStringCommand, "VoteId", DbType.Int64, voteId);
            return this.database.ExecuteNonQuery(sqlStringCommand);
        }

        public bool DeleteVoteItem(long voteId, DbTransaction dbTran)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("DELETE FROM Hishop_VoteItems WHERE VoteId = @VoteId");
            this.database.AddInParameter(sqlStringCommand, "VoteId", DbType.Int64, voteId);
            return (this.database.ExecuteNonQuery(sqlStringCommand, dbTran) > 0);
        }

        public VoteInfo GetVoteById(long voteId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT *, (SELECT ISNULL(SUM(ItemCount),0) FROM Hishop_VoteItems WHERE VoteId = @VoteId) AS VoteCounts FROM Hishop_Votes WHERE VoteId = @VoteId");
            this.database.AddInParameter(sqlStringCommand, "VoteId", DbType.Int64, voteId);
            VoteInfo info = null;
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info = DataMapper.PopulateVote(reader);
                }
            }
            return info;
        }

        public int GetVoteCounts(long voteId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT ISNULL(SUM(ItemCount),0) FROM Hishop_VoteItems WHERE VoteId = @VoteId");
            this.database.AddInParameter(sqlStringCommand, "VoteId", DbType.Int64, voteId);
            return (int) this.database.ExecuteScalar(sqlStringCommand);
        }

        public VoteItemInfo GetVoteItem(long voteItemId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_VoteItems WHERE VoteItemId = @VoteItemId");
            this.database.AddInParameter(sqlStringCommand, "VoteItemId", DbType.Int64, voteItemId);
            VoteItemInfo info = null;
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                if (reader.Read())
                {
                    info = DataMapper.PopulateVoteItem(reader);
                }
            }
            return info;
        }

        public IList<VoteItemInfo> GetVoteItems(long voteId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_VoteItems WHERE VoteId = @VoteId");
            this.database.AddInParameter(sqlStringCommand, "VoteId", DbType.Int64, voteId);
            IList<VoteItemInfo> list = new List<VoteItemInfo>();
            using (IDataReader reader = this.database.ExecuteReader(sqlStringCommand))
            {
                VoteItemInfo item = null;
                while (reader.Read())
                {
                    item = DataMapper.PopulateVoteItem(reader);
                    list.Add(item);
                }
            }
            return list;
        }

        public DataSet GetVotes(bool? isBackup = new bool?())
        {
            string query = string.Empty;
            if (isBackup.HasValue)
            {
                query = "SELECT * FROM Hishop_Votes WHERE IsBackup = 1; SELECT * FROM Hishop_VoteItems WHERE  voteId IN (SELECT voteId FROM Hishop_Votes WHERE IsBackup = 1)";
            }
            else
            {
                query = "SELECT *, (SELECT ISNULL(SUM(ItemCount),0) FROM Hishop_VoteItems WHERE VoteId = Hishop_Votes.VoteId) AS VoteCounts FROM Hishop_Votes";
            }
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand(query);
            return this.database.ExecuteDataSet(sqlStringCommand);
        }

        public int SetVoteIsBackup(long voteId)
        {
            DbCommand storedProcCommand = this.database.GetStoredProcCommand("cp_Votes_IsBackup");
            this.database.AddInParameter(storedProcCommand, "VoteId", DbType.Int64, voteId);
            return this.database.ExecuteNonQuery(storedProcCommand);
        }

        public bool UpdateVote(VoteInfo vote, DbTransaction dbTran)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE Hishop_Votes SET VoteName = @VoteName, MaxCheck = @MaxCheck WHERE VoteId = @VoteId");
            this.database.AddInParameter(sqlStringCommand, "VoteName", DbType.String, vote.VoteName);
            this.database.AddInParameter(sqlStringCommand, "MaxCheck", DbType.Int32, vote.MaxCheck);
            this.database.AddInParameter(sqlStringCommand, "VoteId", DbType.Int64, vote.VoteId);
            return (this.database.ExecuteNonQuery(sqlStringCommand, dbTran) == 1);
        }

        public int Vote(long voteItemId)
        {
            DbCommand sqlStringCommand = this.database.GetSqlStringCommand("UPDATE Hishop_VoteItems SET ItemCount = ItemCount + 1 WHERE VoteItemId = @VoteItemId");
            this.database.AddInParameter(sqlStringCommand, "VoteItemId", DbType.Int32, voteItemId);
            return this.database.ExecuteNonQuery(sqlStringCommand);
        }
    }
}

